In less than a week's time, all sport lovers will be glued to the Mega event of the world's most watched sport, FIFA 2010 Soccer World Cup in South Africa.
Since long I had this fascination for having some webpages that get updated automatically based on the results of the matches being played. Some place where I can find the new updated team rankings in each group after every game. The prevailing positions of the teams in a group mid-way through the Group Stage, that help to come up with all the possible permutations for finding out which teams will make it to the Knockout stages. At the end of the Group Stages, an automatically updated list of the matches in the Knockout Stages. The Knockout matches getting updated automatically with teams progressing towards the Finals while the losers getting dropped.
In the past one year, I have been having some great fun with Excel functions. I was looking for some spreadsheet magic that would help me achieve all the above things and makes following the World Cup even more fun. Couple of weeks back I came across an old template in the
Google Spreadsheets Template Gallery, that did pretty much the things that I was looking for. It had some bugs in the formulae it used. I fixed them and used it as a starting point to create an excel sheet that does just what I wanted. Thanks to the Google Docs feature of submitting spreadsheets as templates, I have now published my
FIFA 2010 Soccer World Cup Template for all soccer fans out there.
Here is a list of some basic things that you will find in this Spreadsheet.
- The Google Spreadsheet contains the list of all the matches in chronological order.
- Users of this Google Spreadsheet only need to update the scores for each match
- Based on the scores for every match, the teams ranking in each individual group is decided
- There is a "Groups" sheet that one can view to compute the permutations needed for their favourite team to make it to the Knockout Stage
- The "Groups" sheet contains the points and also the Goal Difference that comes in handy for deciding the Group leaders in case of a tie
- At the end of the Group Stage, the draws for the Knockout matches get updated automatically by selecting the best 2 teams in each group
- Even for the Knockout matches, users will have to update only the scores, that too in the same page as the Group Stage
- There is a neat "Knockout" sheet that gives a pictorial view of the matches leading to the Final Match
Apart from the above basic things needed for following the matches in the tournament, there are few more things that are unique for this spreadsheet
- You can see the snapshot of the matches played on a given day, and its previous day
- Using a simple hidden trick you can use the above snapshot feature to follow through all the matches played in the tournament from the first day
- Make sure you read the "Usage" sheet to find out this simple hidden trick
- There is also a setting in the "Usage" sheet using which you can find out the timings of the matches in your timezone by updating the TimeZone Difference of your country from GMT
- This spreadsheet has been made so generic that you can now use it to follow any future/previous FIFA Soccer World Cup matches
- You need to modify the list of teams in each Group in the first sheet [ named as "Teams" ] and the list of matches in the Group Stage in the second sheet [ named as "Matches" ] and you are done
- Updating the Start Date of the tournament in the "Usage" sheet updates the date field for all the matches in the "Matches" sheet
You can get a
preview of this Google Spreadsheet Template and click on "Use this template" button on the preview page to create your own copy.
I hope people will find this Google Spreadsheet useful for following the FIFA 2010 Soccer World Cup and also the future World Cups. Make sure rate the template if you like it :)